package Student;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class S_updatesc extends JPanel implements ActionListener{
    String saveC=null;
    String saveS=null;
    JTextField 课号1,学号1,学号,课号,成绩;
    JButton 修改,查找;
    public S_updatesc(){
        try {UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());}
        catch(Exception e){System.err.println("不能设置外观: "+e);}
        学号1=new JTextField(20);
        课号1=new JTextField(20);
        课号=new JTextField(20);
        学号=new JTextField(20);
        成绩=new JTextField(20);
        修改=new JButton("修  改");
        查找=new JButton("查  找");
        Box box1=Box.createHorizontalBox();//横放 box
        Box box2=Box.createHorizontalBox();
        //Box box3=Box.createHorizontalBox();
        Box box4=Box.createHorizontalBox();
        Box box5=Box.createHorizontalBox();
        box1.add(new JLabel("课程号:",JLabel.CENTER));
        box1.add(课号);
        box2.add(new JLabel("学  号:",JLabel.CENTER));
        box2.add(学号);
        //box3.add(new JLabel("成  绩:",JLabel.CENTER));
        //box3.add(成绩);
        box4.add(修改);
        box5.add(new JLabel("课程号:",JLabel.CENTER));
        box5.add(课号1);
        box5.add(new JLabel("学  号:",JLabel.CENTER));
        box5.add(学号1);
        box5.add(查找);
        Box boxH=Box.createVerticalBox();//竖放 box
        boxH.add(box1);
        boxH.add(box2);
        //boxH.add(box3);
        boxH.add(box4);
        boxH.add(Box.createVerticalGlue());
        修改.addActionListener(this);
        查找.addActionListener(this);
        JPanel picPanel=new JPanel();
        JPanel messPanel=new JPanel();
        messPanel.add(box5);
        picPanel.add(boxH);
        setLayout(new BorderLayout());
        JSplitPane splitV=new JSplitPane(JSplitPane.VERTICAL_SPLIT,messPanel,picPanel);//分割
        add(splitV,BorderLayout.CENTER);
        validate();
    }
    public void actionPerformed(ActionEvent e){
        Object obj=e.getSource();
        Statement stmt=null;
        ResultSet rs=null,rs1=null,rsC=null,rsS=null;
        String sql,sql1,sqlS,sqlC;
        if(obj== 查找 ){if( 课号1.getText().equals("")|| 学号1.getText().equals(""))JOptionPane.showMessageDialog(this,"请填写完成查询的信息！ " );
else{
                sql1="select * from xuankebiao where kecheng_id='"+课号1.getText()+"' and xeuhao='"+学号1.getText()+"'";
                try{
                    Connection dbConn1=CONN();
                    stmt=(Statement)dbConn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                    rs1=stmt.executeQuery(sql1);
                    if(rs1.next()){课号.setText(rs1.getString("kecheng_id").trim());
                        学号.setText(rs1.getString("xuehao").trim());
                        成绩.setText(rs1.getString("null").trim());
                        saveC=课号1.getText().trim();
                        saveS=学号1.getText().trim();
                    }
                    else{JOptionPane.showMessageDialog(this,"没有这个课号的学生" );}
                    stmt.close();
                    rs1.close();
                }catch(SQLException e1){
                    System.out.print("SQL Exception occur.Message is:"+e1.getMessage());
                }
            }
        }
else{
            if(obj==修改){if(saveC==null||saveS==null)JOptionPane.showMessageDialog(this,"还 没查找需要修改的学生/课程" );
            else{
                if(课号.getText().equals("")||学号.getText().equals("")){
                    JOptionPane.showMessageDialog(this,"课程信息填满才能修改！ " );
                }
                else{
                    sqlC="select * from kecheng where kecheng_id='"+课号.getText()+"'";
                    sqlS="select * from xuesheng where xuesheng_id='"+学号.getText()+"'";
                    sql1="select * from xuankebiao where kecheng_id='"+ 课号 .getText()+"' and xuehao='"+ 学号.getText()+"'";
                    sql="update xuankebiao set kecheng_id='"+课号.getText()+"',xuehao='"+学号.getText()+"',Score='"+成绩.getText()+"' where kecheng_id='"+saveC+"' and xuehao='"+saveS+"'";
                    try{
                        Connection dbConn1=CONN();
                        stmt=(Statement)dbConn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                        rsC=stmt.executeQuery(sqlC);
                        if(rsC.next()){ rsS=stmt.executeQuery(sqlS);
                            if(rsS.next()){
                                if(课号.getText().trim().equals(saveC)&& 学号.getText().trim().equals(saveS)){
                                    stmt.executeUpdate(sql);
                                    JOptionPane.showMessageDialog(this,"添加成功");
                                    saveC=null;
                                    saveS=null;
                                }
                                else{rs1=stmt.executeQuery(sql1);
                                    if(rs1.next()){JOptionPane.showMessageDialog(this,"学生与课程号以存在， 无法 修改");}
else{
                                            stmt.executeUpdate(sql);
                                            JOptionPane.showMessageDialog(this,"添加成功");
                                            saveC=null;
                                            saveS=null;
                                        }
                                        rs1.close();
                                    }
                                }
else{JOptionPane.showMessageDialog(this,"该学生不存在， 无法修改");}
                                rsS.close();
                            }
                            else{JOptionPane.showMessageDialog(this,"该课程不存在， 无法修改");}
                            rsC.close();
                            stmt.close();
                        }
catch(SQLException e1){
                            System.out.print("SQL Exception occur.Message is:"+e1.getMessage());
                        }
                    }
                }
            }
            }
        }
// 连接数据库方法
        public static Connection CONN(){
            String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加 载JDBC 驱动
            String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=master"; //连接服务器和数据库 test
            String userName = "sa"; //默认用户名
            String userPwd = "123456"; //密码
            Connection dbConn=null;
            try {
                Class.forName(driverName);
                dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
                System.out.println("Connection Successful!"); //如果连接成功 控制台 输出Connection Successful!
            } catch (Exception e) {
                e.printStackTrace();
            }
            return dbConn;
        }
    }